﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.OracleClient;
using System.Configuration;
using System.Text;

public partial class IRSTrade : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
            GridView1.Visible = false;
    }
    protected void queryBtn_Click(object sender, EventArgs e)
    {
        using (TR_DBDataContext db = new TR_DBDataContext())
        {
            // 取得ODS交易資料
            using (OracleConnection conn = new OracleConnection())
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString_ODS"].ConnectionString;
                conn.Open();
                OracleCommand cmd = conn.CreateCommand();
                StringBuilder sb = new StringBuilder();
                sb.Append("SELECT * FROM OD_OB_SWAP WHERE TRDSTATUS='VER' AND SUBTYPE='IRSWAP' AND PAYBOOK NOT IN ('CRCB', 'CMINV') AND RECBOOK NOT IN ('CR_CB','CMINV') ");
                sb.Append("AND TRDDT=TO_DATE('" + reportDate.Text + "','yyyy/mm/dd')");
                //For產生Outstanding的Trade Data
                //sb.Append("AND ENDDATE>=TO_DATE('" + reportDate.Text + "','yyyy/mm/dd')");
                
                string sqlStr = sb.ToString();
                cmd.CommandText = sqlStr;

                // Read oracle data
                OracleDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    DateTime tradeDate = reader["TRDDT"] != DBNull.Value ? (DateTime)reader["TRDDT"] : DateTime.Now;
                    string tradeID = reader["TRDID"] != DBNull.Value ? ((string)reader["TRDID"]).TrimEnd().ToString() : "";
                    var rptData = from P in db.TR_FormatA_IRS where P.TradeDate.Date.Equals(tradeDate) && P.SequenceNo.Trim().Equals(tradeID) select P;
                    
                    TR_FormatA_IRS formatA;
                    if (rptData.Count() > 0)
                        formatA = (from P in db.TR_FormatA_IRS where P.TradeDate.Date.Equals(tradeDate) && P.SequenceNo.Trim().Equals(tradeID) select P).First();
                    else
                        formatA = new TR_FormatA_IRS();

                    // Table 欄位資料設定
                    formatA.ProductType = '1';
                    formatA.SequenceNo = tradeID;
                    formatA.BankCode = "8050000";
                    formatA.BankType = '1';
                    switch (reader["COMPANY"] != DBNull.Value ? ((string)reader["COMPANY"]).TrimEnd().ToString() : "")
                    {
                        case "DBU":
                            formatA.TransUnit = '1';
                            break;
                        case "OBU":
                            formatA.TransUnit = '2';
                            break;
                        case "HK":
                            formatA.TransUnit = '3';
                            break;
                        case "":
                            formatA.TransUnit = '0';
                            break;
                    }
                    //取得CustomerDefinition資料
                    Customer customer = new Customer();
                    customer.customerID = reader["CUST"] != DBNull.Value ? ((string)reader["CUST"]).TrimEnd().ToString() : "";
                    customer.getCustDataFromODS();
                    //取得ItemList資料
                    ItemList itemList = new ItemList();
                    itemList.itemCode = customer.groupID;
                    itemList.itemType = "CptyType";
                    itemList.getItemFromItemList();
                    formatA.CptyType = Convert.ToChar(itemList.itemName);
                    formatA.CptyName = customer.fullName;
                    switch(formatA.CptyType){
                        case '1':
                            BankCodeMaping bankMaping = new BankCodeMaping();
                            bankMaping.summitCustID = reader["CUST"] != DBNull.Value ? ((string)reader["CUST"]).TrimEnd().ToString() : "";
                            bankMaping.getBankCodeMapBySummitID();
                            formatA.CptyCode = bankMaping.fiscBankCode;
                            break;
                        case '2':
                        case '3':
                            formatA.CptyCode = customer.unino.Trim();
                            break;
                        case '4':
                        case '5':
                        case '6':
                        default:
                            formatA.CptyCode = "";
                            break;
                    }
                    string payBook = reader["PAYBOOK"] != DBNull.Value ? ((string)reader["PAYBOOK"]).TrimEnd().ToString() : "";
                    switch(payBook){
                        case "T DER_HK":
                        case "T_DER_AS":
                        case "T_DER_HO":
                        case "T_DER_TR1":
                            formatA.TransReason = 'Z';
                            break;
                        case "H_DER":
                            formatA.TransReason = '4';
                            break;
                        default:
                            formatA.TransReason = 'Z';
                            break;
                    }
                    formatA.TradeDate = reader["TRDDT"] != DBNull.Value ? (DateTime)reader["TRDDT"] : DateTime.MaxValue;
                    formatA.EffectiveDate = reader["STARTDATE"] != DBNull.Value ? (DateTime)reader["STARTDATE"] : DateTime.MaxValue;
                    formatA.MaturityDate = reader["ENDDATE"] != DBNull.Value ? (DateTime)reader["ENDDATE"] : DateTime.MaxValue;
                    // 期初收付幣別
                    formatA.FirstPayRecCcy = "";
                    // 期初收付金額
                    formatA.FirstPayRecAmt = Convert.ToDecimal(0.00);
                    //名目本金幣別
                    formatA.NotionalCcy = Common.replaceCNHToCNY(reader["PAYCCY"] != DBNull.Value ? ((string)reader["PAYCCY"]).TrimEnd().ToString() : "");
                    //名目本金金額
                    formatA.NotionalAmt = reader["PAYNTL"] != DBNull.Value ? Convert.ToDecimal(reader["PAYNTL"]) : Convert.ToDecimal("0.00");
                    // 利息收付方向
                    string payIndex = reader["PAYINDEX"] != DBNull.Value ? ((string)reader["PAYINDEX"]).TrimEnd().ToString() : "";
                    string recIndex = reader["RECINDEX"] != DBNull.Value ? ((string)reader["RECINDEX"]).TrimEnd().ToString() : "";
                    if (payIndex.Trim().ToUpper().Equals("FIXED"))
                        formatA.IndexType = '1';
                    else
                        if (recIndex.Trim().ToUpper().Equals("FIXED"))
                            formatA.IndexType = '2';
                        else
                            formatA.IndexType = '3';
                    // 營業日原則(PAYSCHED_PAY_RULE)
                    string payRull = reader["PAYSCHED_PAY_RULE"] != DBNull.Value ? ((string)reader["PAYSCHED_PAY_RULE"]).TrimEnd().ToString() : "";
                    switch (payRull)
                    {
                        case "":
                            formatA.RollDateType = '1';
                            break;
                        case "F":
                            formatA.RollDateType = '2';
                            break;
                        case "P":
                            formatA.RollDateType = '4';
                            break;
                        case "MF":
                            formatA.RollDateType = '3';
                            break;
                        case "MP":
                            formatA.RollDateType = '5';
                            break;
                        case "E":
                        case "SA":
                        default:
                            formatA.RollDateType = 'Z';
                            break;
                    }
                    // 是否複利計算
                    formatA.isCompounding = '1';
                    // 利息交割方式
                    formatA.RateSettleType = '1';
                    // 固定利率(PAYINTERESTRATE,RECINTERESTRATE)
                    if (payIndex.Trim().Equals("FIXED"))
                        formatA.FixRate = reader["PAYINTERESTRATE"] != DBNull.Value ? Convert.ToDecimal(reader["PAYINTERESTRATE"]) * 100: Convert.ToDecimal("0.00");
                    else
                        formatA.FixRate = reader["RECINTERESTRATE"] != DBNull.Value ? Convert.ToDecimal(reader["RECINTERESTRATE"]) * 100: Convert.ToDecimal("0.00");
                    // 付息周期(PAYSCHED_PAY_FREQ)
                    formatA.FixPaySchedule = string.Empty;
                    itemList = new ItemList();
                    itemList.itemType = "FreqCode";
                    if (payIndex.Trim().ToUpper().Equals("FIXED"))
                        itemList.itemCode = reader["PAYSCHED_PAY_FREQ"] != DBNull.Value ? ((string)reader["PAYSCHED_PAY_FREQ"]).TrimEnd().ToString() : "";
                    else
                        if (recIndex.Trim().ToUpper().Equals("FIXED"))
                            itemList.itemCode = reader["RECSCHED_PAY_FREQ"] != DBNull.Value ? ((string)reader["RECSCHED_PAY_FREQ"]).TrimEnd().ToString() : "";
                    itemList.getItemFromItemList();
                    formatA.FixPaySchedule = itemList.itemName;
                    // 計息天數(PAYINTERESTBASIS,PAYINTERESTBASIS)
                    string basis = string.Empty;
                    if(payIndex.Trim().ToUpper().Equals("FIXED"))
                        basis = reader["PAYINTERESTBASIS"] != DBNull.Value ? ((string)reader["PAYINTERESTBASIS"]).TrimEnd().ToString() : "";
                    if (recIndex.Trim().ToUpper().Equals("FIXED"))
                        basis = reader["RECINTERESTBASIS"] != DBNull.Value ? ((string)reader["RECINTERESTBASIS"]).TrimEnd().ToString() : "";
                    formatA.FixBasis = Common.getBasisCode(basis);
                    // 浮動利率-利率指標
                    formatA.FloatIndex = string.Empty;
                    // 浮動利率-利率指標天期
                    formatA.FloatIntTermType = string.Empty;
                    // 浮動利率-付息周期
                    formatA.FloatTerm = string.Empty;
                    // 浮動利率-利率加減碼
                    formatA.FloatSpread = Convert.ToDecimal(0.00);
                    // 浮動利率-利率指標重設周期
                    formatA.FloatResetType = string.Empty;
                    // 浮動利率-計息天數
                    formatA.FloatBasis = char.MinValue;
                    
                    // 浮動利率2-利率指標
                    formatA.Float1Index = string.Empty; ;
                    // 浮動利率2-利率指標天期(PAYINTERESTTERM)
                    formatA.Float1IntTermType = string.Empty;
                    // 浮動利率2-付息周期
                    formatA.Float1Term = string.Empty;
                    // 浮動利率2-利率加減碼(PAYINTERESTSPREAD)
                    formatA.Float1Spread = Convert.ToDecimal(0.00);
                    // 浮動利率2-利率指標重設周期
                    formatA.Float1ResetType = string.Empty;
                    // 浮動利率2-計息天數
                    formatA.Float1Basis = char.MinValue;
                    string payCcy = Common.replaceCNHToCNY(reader["PAYCCY"] != DBNull.Value ? ((string)reader["PAYCCY"]).TrimEnd().ToString() : "");
                    string recCcy = Common.replaceCNHToCNY(reader["RECCCY"] != DBNull.Value ? ((string)reader["RECCCY"]).TrimEnd().ToString() : "");
                    if (payIndex.Trim().ToUpper().Equals("FIXED"))
                    {
                        formatA.FloatIndex = Common.getAssetCodeMaping(recCcy, recIndex);
                        formatA.FloatIntTermType = reader["RECINTERESTTERM"] != DBNull.Value ? ((string)reader["RECINTERESTTERM"]).TrimEnd().ToString() : "";
                        formatA.FloatTerm = reader["RECINTERESTTERM"] != DBNull.Value ? ((string)reader["RECINTERESTTERM"]).TrimEnd().ToString() : "";
                        formatA.FloatResetType = reader["RECINTERESTTERM"] != DBNull.Value ? ((string)reader["RECINTERESTTERM"]).TrimEnd().ToString() : "";
                        formatA.FloatSpread = reader["RECINTERESTSPREAD"] != DBNull.Value ? Convert.ToDecimal(reader["RECINTERESTSPREAD"]) : Convert.ToDecimal("0.00");
                        formatA.FloatBasis = Common.getBasisCode(reader["RECINTERESTBASIS"] != DBNull.Value ? ((string)reader["RECINTERESTBASIS"]).TrimEnd().ToString() : "");
                    }
                    else
                    {
                        //Float 1
                        formatA.FloatIndex = Common.getAssetCodeMaping(payCcy, payIndex);
                        formatA.FloatIntTermType = reader["PAYINTERESTTERM"] != DBNull.Value ? ((string)reader["PAYINTERESTTERM"]).TrimEnd().ToString() : "";
                        formatA.FloatTerm = reader["PAYINTERESTTERM"] != DBNull.Value ? ((string)reader["PAYINTERESTTERM"]).TrimEnd().ToString() : "";
                        formatA.FloatResetType = reader["PAYINTERESTTERM"] != DBNull.Value ? ((string)reader["PAYINTERESTTERM"]).TrimEnd().ToString() : "";
                        formatA.FloatSpread = reader["PAYINTERESTSPREAD"] != DBNull.Value ? Convert.ToDecimal(reader["PAYINTERESTSPREAD"]) : Convert.ToDecimal("0.00");
                        formatA.FloatBasis = Common.getBasisCode(reader["PAYINTERESTBASIS"] != DBNull.Value ? ((string)reader["PAYINTERESTBASIS"]).TrimEnd().ToString() : "");
                        if (!recIndex.Trim().ToUpper().Equals("FIXED"))
                        {
                            //Float 2
                            formatA.Float1Index = Common.getAssetCodeMaping(recCcy, recIndex);
                            formatA.Float1IntTermType = reader["RECINTERESTTERM"] != DBNull.Value ? ((string)reader["RECINTERESTTERM"]).TrimEnd().ToString() : "";
                            formatA.Float1Term = reader["RECINTERESTTERM"] != DBNull.Value ? ((string)reader["RECINTERESTTERM"]).TrimEnd().ToString() : "";
                            formatA.Float1ResetType = reader["RECINTERESTTERM"] != DBNull.Value ? ((string)reader["RECINTERESTTERM"]).TrimEnd().ToString() : "";
                            formatA.Float1Spread = reader["RECINTERESTSPREAD"] != DBNull.Value ? Convert.ToDecimal(reader["RECINTERESTSPREAD"]) : Convert.ToDecimal("0.00");
                            formatA.Float1Basis = Common.getBasisCode(reader["RECINTERESTBASIS"] != DBNull.Value ? ((string)reader["RECINTERESTBASIS"]).TrimEnd().ToString() : "");
                        }
                    }
                    formatA.TradeType = "IRS";

                    if (rptData.Count() <= 0)
                        db.TR_FormatA_IRS.InsertOnSubmit(formatA);
                    db.SubmitChanges();
                }// end of while()
            }// end of using()
        }// end of using()

        // 顯示報表資料
        displayMode();
    }
    private void displayMode()
    {
        GridView1.Visible = true;
    }
    protected void exportToCsv_Click(object sender, EventArgs e)
    {
    }
    protected void GridView1_DataBound(object sender, EventArgs e)
    {
        GridView gv = (GridView)sender;
        
    }
}
